-- $Id: docket_violation_code_id.sql 122 2008-05-26 21:09:25Z doug.hellmann $
--
-- Convert the docket_violation.code column to a foreign key reference to the
-- violation table.
--

BEGIN;

-- Add the column
ALTER TABLE docket_violation 
  ADD COLUMN  "code_id" integer REFERENCES "docket_violationcode" ("id");
UPDATE docket_violation SET code_id = (SELECT id FROM docket_violationcode WHERE docket_violation.code = docket_violationcode.code);

-- Add the constraint for the new column
ALTER TABLE docket_violation ALTER COLUMN "code_id" SET NOT NULL;

-- Remove the code column
ALTER TABLE docket_violation DROP COLUMN "code";

COMMIT;